package cn.kgc.day05.dao.impl;


import cn.kgc.day05.dao.ScoreDao;
import cn.kgc.day05.entity.Score;
import cn.kgc.day05.util.JDBCUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.sql.DataSource;
import java.sql.SQLException;
import java.util.List;

/**
 * @author 课工场
 * @date 2024/10/11
 * @description   查询数据库
 * jdbc操作
 *    1. 导包
 *    2. 注册驱动
 *    3. 获取连接对象
 *    4. 获取发送sql语句的对象 Statement
 *    5. 编写sql  发送sql
 *    6. 处理结果集
 *    7. 释放资源

 */
public class ScoreDaoImpl implements ScoreDao {
    @Override
    public List<Score> selectScoreList() {

        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "select * from score";
        try {
            List<Score> scores = queryRunner.query(sql, new BeanListHandler<Score>(Score.class));
            return scores;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer insertScore(Score s) {

        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "insert into score (name,course,score) values(?,?,?)";

        try {
            int update = queryRunner.update(sql, s.getName(), s.getCourse(), s.getScore());
            return  update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Score selectScoreById(Integer dbId) {
        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "select * from score where id = ?";

        try {
            Score score = queryRunner.query(sql, new BeanHandler<Score>(Score.class),dbId);
            return score;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer editScore(Score s) {

        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "update score set name = ? ,score = ? ,course= ? ,version = version+1 where id = ? and version = ?";
        try {
            int update = queryRunner.update(sql, s.getName(), s.getScore(), s.getCourse(),s.getId(),s.getVersion());
            return  update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    @Override
    public Integer deleteById(Integer id) {

        DataSource dataSource = JDBCUtil.getDataSource();
        QueryRunner queryRunner = new QueryRunner(dataSource);
        String sql = "delete from score where id = ?";

        try {
            int update = queryRunner.update(sql,id);
            return  update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public List<Score> scorePage(int start, Integer limit) {

        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select * from score  order by  id desc limit ?,?";

        try {
            List<Score> scoreList = queryRunner.query(sql, new BeanListHandler<Score>(Score.class), start, limit);
            return scoreList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Long selectTotal() {

        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        String sql = "select count(*) from score";
        try {
            Long total = queryRunner.query(sql, new ScalarHandler<>());
            return  total;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

}
